package com.andnnl.clickhouse;

/**
 * Created by chenss on 2021/8/10.
 */
public class MssqlToClickHouse {
    public static String changeMysqlTableToClickHouse(String sql) {
        String tables = sql.replaceAll("\\[dbo\\]\\.","").replaceAll("[\\[\\]]","");

        String[] rows = tables.split("\n");
        String replaceTables = "";
        int i = 0;
        for (String row : rows) {
            if (row.contains("KEY")) {
                continue;
            }
            if (row.contains(") ENGINE=InnoDB")) {
                row = ") ENGINE = Memory";
            }
            String changeRow = row.replaceAll(" NOT NULL", "")
                    .replaceAll("IDENTITY\\(1,1\\)", "")
                    .replaceAll("AUTO_INCREMENT", "")
                    .replaceAll("CHARACTER SET utf8mb4", "")
                    .replaceAll("CHARACTER SET utf8", "")
                    .replaceAll("ON UPDATE CURRENT_TIMESTAMP", "")
                    .replaceAll("CURRENT_TIMESTAMP", "")
                    .replaceAll("datetime( NULL)", "Nullable(DateTime)")
                    .replaceAll("datetime", "DateTime");

            changeRow = changeRow.replaceAll("n?varchar\\(\\d+\\) NULL", "Nullable(String)");
            changeRow = changeRow.replaceAll("n?varchar\\(\\d+\\)", "String");
            changeRow = changeRow.replaceAll("text", "String");
            changeRow = changeRow.replaceAll("smallint( NULL)", "Nullable(Int8)");
            changeRow = changeRow.replaceAll("smallint( MOT NULL)?", "Int8");
            changeRow = changeRow.replaceAll("tinyint( NULL)", "Nullable(Int8)");
            changeRow = changeRow.replaceAll("tinyint( MOT NULL)?", "Int8");
            changeRow = changeRow.replaceAll("bigint( NULL)", "Nullable(Int64)");
            changeRow = changeRow.replaceAll("bigint( MOT NULL)?", "Int64");
            changeRow = changeRow.replaceAll("int( NULL)", "Nullable(Int32)");
            changeRow = changeRow.replaceAll("int( MOT NULL)?", "Int32");
            changeRow = changeRow.replaceAll("money( NULL)", "Nullable(Decimal64(4))");
            changeRow = changeRow.replaceAll("money( MOT NULL)?", "Decimal64(4)");
            String[] changeColumns = changeRow.split("[ ]");
 System.out.println(changeRow);

            replaceTables += changeRow+"\n";
            i++;
        }
        if (replaceTables.contains(",) ENGINE = Memory")) {
            String temp = replaceTables.substring(0, replaceTables.indexOf(",) ENGINE = Memory"));
            replaceTables = temp + ") ENGINE = Memory ";
        }
        replaceTables.replaceAll("CREATE TABLE `" + sql + "`", sql + "_local");
        return replaceTables+" ENGINE =MergeTree()\n" +
                " order by(id) ";
    }

    public static void main(String[] args) {
//这里是用脚本跑出来后粘上去的。show create table XXX表名;
//用mybatis连接数据库查询出来建表语句比较好
        /*String createTable = "CREATE TABLE `XXX表名` (\n" +
                " `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键(自动递增)',\n" +
                " `user_id` bigint(20) NOT NULL COMMENT '用户id',\n" +
                " `user_name` varchar(255) DEFAULT NULL COMMENT '用户名、账号',\n" +
                " `table_id` varchar(255) DEFAULT NULL COMMENT '当前的表名',\n" +
                " `filed` varchar(500) DEFAULT NULL COMMENT '存放有权限的字段',\n" +
                " `database_id` varchar(100) DEFAULT NULL COMMENT '主题数据库',\n" +
                " `is_del` varchar(1) DEFAULT NULL COMMENT '删除状态 ',\n" +
                " `created_date` datetime DEFAULT NULL COMMENT '创建时间',\n" +
                " `created_by` varchar(255) DEFAULT NULL COMMENT '创建人id',\n" +
                " `created_name` varchar(255) DEFAULT NULL COMMENT '创建人姓名',\n" +
                " `updated_date` datetime DEFAULT NULL COMMENT '更新时间',\n" +
                " `updated_by` varchar(255) DEFAULT NULL COMMENT '更新人id',\n" +
                " `updated_name` varchar(255) DEFAULT NULL COMMENT '更新人name',\n" +
                " `flag` varchar(1) DEFAULT NULL COMMENT '0:待审核 1:通过 2:未通过',\n" +
                " `apply_reason` varchar(255) COMMENT '申请原因',\n" +
                " `ch_name` varchar(50) DEFAULT NULL COMMENT '表中文名',\n" +
                " `descri_table` varchar(50) DEFAULT NULL COMMENT '表描述',\n" +
                " `owner` varchar(50) DEFAULT NULL COMMENT '表的所有者',\n" +
                " `audit_advice` varchar(255) DEFAULT NULL COMMENT '审批意见',\n" +
                " `db_comment` varchar(100) DEFAULT NULL COMMENT '业务名称(数据库描述)',\n" +
                " `db_class` varchar(100) DEFAULT NULL COMMENT '业务分类',\n" +
                " `table_record_id` varchar(50) DEFAULT NULL COMMENT '表的记录Id',\n" +
                " PRIMARY KEY (`id`)\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COMMENT='数据权限申请记录表'";*/
        String createTable="CREATE TABLE [dbo].[yh_temp_label](\n" +
                "\t[id] [int] IDENTITY(1,1) NOT NULL,\n" +
                "\t[tempId] [int] NULL,\n" +
                "\t[labelId] [int] NULL,\n" +
                "\t[createTime] [datetime] NULL)";
        String res = changeMysqlTableToClickHouse(createTable);
        System.out.println("转换后的建表语句为：" + res);
    }
}
